insert overwrite table jms_dm.dm_reback_analyse_detail_dt partition(dt='{{ execution_date | cst_ds }}')
select
      waybill_no                      --运单号
     ,order_source_code               --订单来源编码
     ,order_source_name               --订单来源名称
     ,register_time                   --登记时间
     ,register_network_code           --登记网点code
     ,register_network_name           --登记网点名称
     ,register_agent_code             --登记网点所属代理区编码
     ,register_agent_name             --登记网点所属代理区名称
     ,print_time                      --打印时间
     ,print_network_code              --打印网点编码
     ,print_network_name              --打印网点名称
     ,print_agent_code                --打印网点所属代理区编码
     ,print_agent_name                --打印网点所属代理区名称
     ,print_network_type              --操作类型 登记网点=派件，登记集散点=转运
     ,proble_first_type_code          --问题件一级类型编码
     ,proble_first_type_name          --问题件一级类型名称
     ,proble_second_type_code         --问题件二级类型编码
     ,proble_second_type_name         --问题件二级类型名称
     ,proble_description              --问题件原因描述
     ,proble_register_date            --问题件登记时间
     ,proble_register_cnt             --问题件登记次数
     ,proble_first_reply_time         --问题件首次回复时间
     ,proble_reply_time               --回复时长=回复时间-问题件登记时间
     ,proble_receive_network_code     --问题件接收网点编码
     ,proble_receive_network_name     --问题件接收网点名称
     ,proble_receive_agent_code       --问题件接收网点所属代理区编码
     ,proble_receive_agent_name       --问题件接收网点所属代理区名称
     ,if_work                         --是否有客诉
     ,work_reason                     --客户投诉原因
from(
    select
          reback.waybill_no                as waybill_no                       --运单号
         ,der.order_source_code            as order_source_code                --订单来源编码
         ,der.order_source_name            as order_source_name                --订单来源名称
         ,reback.scan_time                 as register_time                    --登记时间
         ,reback.scan_site_code            as register_network_code            --登记网点code
         ,reback.scan_site                 as register_network_name            --登记网点名称
         ,register.agent_code              as register_agent_code              --登记网点所属代理区编码
         ,register.agent_name              as register_agent_name              --登记网点所属代理区名称
         ,reback.print_time                as print_time                       --打印时间
         ,reback.print_network_code        as print_network_code               --打印网点编码
         ,reback.print_network_name        as print_network_name               --打印网点名称
         ,print.agent_code                 as print_agent_code                 --打印网点所属代理区编码
         ,print.agent_name                 as print_agent_name                 --打印网点所属代理区名称
         ,case when register.network_type = 6 then '派件'
               when register.network_type = 5 then '转运'
               end as print_network_type                                       --操作类型 登记网点=派件，登记集散点=转运
         ,proble.proble_type_subject_code as proble_first_type_code            --问题件一级类型编码
         ,proble.proble_type_subject_name as proble_first_type_name            --问题件一级类型名称
         ,proble.second_level_type_code   as proble_second_type_code           --问题件二级类型编码
         ,proble.second_level_type_name   as proble_second_type_name           --问题件二级类型名称
         ,proble.proble_description       as proble_description                --问题件原因描述
         ,proble.scan_time                as proble_register_date              --问题件登记时间
         ,case when reback.print_network_code = proble.registration_network_code then proble.cn else 0 end as proble_register_cnt --问题件登记次数
         ,proble.first_reply_time         as proble_first_reply_time           --问题件首次回复时间
         ,((unix_timestamp(proble.first_reply_time) - unix_timestamp(proble.scan_time)) / 60) as proble_reply_time --回复时长=回复时间-问题件登记时间
         ,proble.receive_network_code     as proble_receive_network_code       --问题件接收网点编码
         ,proble.receive_network_name     as proble_receive_network_name       --问题件接收网点名称
         ,receive.agent_code              as proble_receive_agent_code         --问题件接收网点所属代理区编码
         ,receive.agent_name              as proble_receive_agent_name         --问题件接收网点所属代理区名称
         ,case when work.waybill_no is not null then '是' else '否' end as if_work      --是否有客诉
         ,work.problem_desc as work_reason  --客户投诉原因
    from (
        select *
        from(
                select
                      waybill_no           --运单号
                     ,scan_time            --申请时间
                     ,scan_site_code       --申请网点code
                     ,scan_site            --申请网点名称
                     ,print_time           --打印时间
                     ,print_network_code   --打印网点编码
                     ,print_network_name   --打印网点名称
                     ,row_number() over(partition by waybill_no order by print_time desc) as rn
                from jms_dwd.dwd_wide_abnormal_union_waybill_dt --问题件宽表
                where type_code = 4 --退件
                  and scan_type = 'reback' --退转件
                  and dt = date_sub('{{ execution_date | cst_ds }}',4)
            ) a where a.rn = 1
    ) reback
    left join (
         select *
         from(
              select
                    waybill_no
                   ,proble_type_subject_code   --问题件一级类型编码
                   ,proble_type_subject_name   --问题件一级类型名称
                   ,second_level_type_code     --问题件二级类型编码
                   ,second_level_type_name     --问题件二级类型名称
                   ,proble_description         --问题件原因描述
                   ,first_reply_time           --回复时间
                   ,receive_network_code       --接收网点编码
                   ,receive_network_name       --接收网点名称
                   ,registration_network_name  --登记网点名称
                   ,registration_network_code  --登记网点编码
                   ,scan_time                  --登记时间
                   ,row_number() over (partition by waybill_no order by create_time,update_time desc) as rn
                   ,count(1) over(partition by waybill_no,registration_network_code) as cn
              from jms_dwd.dwd_sqs_registration_problem_piece_base_dt --问题件表
              where dt between date_sub('{{ execution_date | cst_ds }}',11) and date_sub('{{ execution_date | cst_ds }}',4)
         ) a where a.rn = 1
    ) proble on reback.waybill_no = proble.waybill_no
    left join(
         select
               code
              ,name
              ,agent_code  --代理区code
              ,agent_name  --代理区名称
         from jms_dim.dim_network_whole_massage --网点维表
    ) print on reback.print_network_code = print.code
    left join(
        select
              code
             ,name
             ,agent_code  --代理区code
             ,agent_name  --代理区名称
        from jms_dim.dim_network_whole_massage --网点维表
    ) receive on proble.receive_network_code = receive.code
    left join(
         select
               code
              ,name
              ,agent_code  --代理区code
              ,agent_name  --代理区名称
              ,network_type
         from jms_dim.dim_network_whole_massage --网点维表
    ) register on reback.scan_site_code = register.code
    left join (
        select *
        from (
             select
                 waybill_no,problem_desc
                  ,row_number() over (partition by waybill_no order by create_time ) as rn
             from jms_dwd.dwd_complaint_work_order_detail_dt  --工单表
             where dt between date_sub('{{ execution_date | cst_ds }}',4) and '{{ execution_date | cst_ds }}'
               and work_order_type = 1
        ) a where a.rn = 1
    ) work on work.waybill_no = reback.waybill_no
    left join (
        select
             billcode  as waybill_no
            ,ordersource_code as order_source_code
            ,ordersource_name as order_source_name
        from jms_dwd.dwd_order_source_detail_dt  --订单来源
        where dt between date_sub('{{ execution_date | cst_ds }}',20) and date_sub('{{ execution_date | cst_ds }}',4)
    ) der on der.waybill_no = reback.waybill_no
) a